﻿using System;
using System.Collections.Generic;
using System.Reflection;
using System.Data.SqlClient;
using DavidDB.ADO.Tools;
namespace DavidDB.Reflection.DAL.Managers
{
    /// <summary>
    /// 反射数据库的添删改查基本类
    /// </summary>
    public class SQLManager
    {

        /// <summary>
        /// 返回LogeManager对象并初始化
        /// </summary>
        /// <returns></returns>
        public static SQLManager GetInstance()
        {
            SQLManager lm = new SQLManager();
            return lm;
        }

        /// <summary>
        /// 查询返回表里的所有记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<object> GetList(dynamic obj, SqlConnection conneciton, SqlTransaction trans)
        {
            List<object> tList = new List<object>();
            Type type = obj.GetType();
            string tableName = obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1);
            PropertyInfo[] propertiesa = type.GetProperties();
            string sql = string.Format("select * from {0} order by {1} desc", tableName, propertiesa[0].Name);
            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql, conneciton, trans);
            while (reader.Read())
            {
                var t = Activator.CreateInstance(obj.GetType());
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    if (DBHelper.NeedToParse(p))
                    {
                        string columnName = p.Name;
                        string PType = p.PropertyType.ToString();
                        DBHelper.Operator(p, PType, reader[columnName], t);
                    }
                }
                tList.Add(t);
            }
            reader.Close();

            return tList;
        }

        /// <summary>
        /// 分页返回表里的所有记录
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="PageSize">每页信息量</param>
        /// <param name="NowPage">当前页</param>
        /// <returns></returns>
        public List<object> GetListByPage(int PageSize, int NowPage, out int PageCount, out int recordCount, object obj, SqlTransaction trans, SqlConnection connection)
        {
            List<object> tList = new List<object>();
            Type type = obj.GetType();
            string tableName = obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1);
            PropertyInfo[] propertiesa = type.GetProperties();
            string sql = string.Format("select * from (select *,row_number() over (order by " + propertiesa[0].Name + " desc) as row from {0}) {1} where row between {2} and {3}", tableName, tableName, PageSize * (NowPage - 1) + 1, NowPage * PageSize);
            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql, connection, trans);
            while (reader.Read())
            {
                var t = Activator.CreateInstance(obj.GetType());
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    if (DBHelper.NeedToParse(p))
                    {
                        string columnName = p.Name;
                        string PType = p.PropertyType.ToString();
                        DBHelper.Operator(p, PType, reader[columnName], t);
                    }
                }
                tList.Add(t);
            }
            reader.Close();
            recordCount = DBHelper.GetTableCount(tableName, ADO.DbType.MSSql, propertiesa[0].Name, connection, trans);
            if (recordCount % PageSize == 0)
            {
                PageCount = recordCount / PageSize;
            }
            else
            {
                PageCount = (recordCount / PageSize) + 1;
            }
            return tList;

        }
        /// <summary>
        /// 分页返回表里的所有记录
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="PageSize">每页信息量</param>
        /// <param name="NowPage">当前页</param>
        /// <returns></returns>
        public List<object> GetListByPage(SqlConnection connection, SqlTransaction trans, int PageSize, int NowPage, out int PageCount, out int recordCount, dynamic obj)
        {
            List<object> tList = new List<object>();
            Type type = obj.GetType();
            string tableName = obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1);
            PropertyInfo[] propertiesa = type.GetProperties();
            string sql = string.Format("select * from (select *,row_number() over (order by " + propertiesa[0].Name + " desc) as row from {0}) {1} where row between {2} and {3}", tableName, tableName, PageSize * (NowPage - 1) + 1, NowPage * PageSize);
            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql, connection, trans);
            while (reader.Read())
            {
                var t = Activator.CreateInstance(obj.GetType());
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    if (DBHelper.NeedToParse(p))
                    {
                        string columnName = p.Name;
                        string PType = p.PropertyType.ToString();
                        DBHelper.Operator(p, PType, reader[columnName], t);
                    }
                }
                tList.Add(t);
            }
            reader.Close();
            recordCount = DBHelper.GetTableCount(tableName, ADO.DbType.MSSql, propertiesa[0].Name, connection, trans);
            if (recordCount % PageSize == 0)
            {
                PageCount = recordCount / PageSize;
            }
            else
            {
                PageCount = (recordCount / PageSize) + 1;
            }
            return tList;
        }

        public List<object> GetListByPageLikeKey(SqlConnection connection, SqlTransaction trans, int PageSize, int NowPage, object obj, out int PageCount, out int recordCount)
        {
            List<object> tList = new List<object>();
            Type type = obj.GetType();
            string tableName = obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1);
            PropertyInfo[] myPropertyInfo2 = type.GetProperties();
            string sqlparam = "";
            int paramCount = 0;
            foreach (PropertyInfo p in myPropertyInfo2)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo2[0], p))
                {
                    sqlparam += p.Name + " like @" + p.Name + " and ";
                    paramCount++;
                }
            }
            sqlparam = sqlparam.Substring(0, sqlparam.Length - 4);
            string sql = string.Format("select * from (select *,row_number() over (order by " + myPropertyInfo2[0].Name + " desc) as row from {0} where {1}) {2} where row between {3} and {4}", tableName, sqlparam, tableName, PageSize * (NowPage - 1) + 1, NowPage * PageSize);
            SqlParameter[] param = new SqlParameter[paramCount];
            int cNum = 0;
            foreach (PropertyInfo p in myPropertyInfo2)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo2[0], p))
                {
                    param[cNum] = DBHelper.GetSqlParameter(p);
                    param[cNum].Value = p.GetValue(obj, null);
                    cNum++;

                }
            }
            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql, connection, trans, param);
            while (reader.Read())
            {
                var t = Activator.CreateInstance(obj.GetType());
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    if (DBHelper.NeedToParse(p))
                    {
                        string columnName = p.Name;
                        string PType = p.PropertyType.ToString();
                        DBHelper.Operator(p, PType, reader[columnName], t);
                    }
                }
                tList.Add(t);
            }
            reader.Close();
            recordCount = DBHelper.GetTableCountByKeys(tableName, sqlparam, ADO.DbType.MSSql, myPropertyInfo2[0].Name, connection, trans, param);
            if (recordCount % PageSize == 0)
            {
                PageCount = recordCount / PageSize;
            }
            else
            {
                PageCount = (recordCount / PageSize) + 1;
            }
            return tList;
        }

        /// <summary>
        /// 按条件分页返回表里的所有记录
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="PageSize">每页信息量</param>
        /// <param name="NowPage">当前页</param>
        /// <returns></returns>
        public List<object> GetListByPageAndKeys(SqlConnection connection, SqlTransaction trans, int PageSize, int NowPage, object obj, out int PageCount, out int recordCount)
        {
            List<object> tList = new List<object>();
            Type type = obj.GetType();
            string tableName = obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1);
            PropertyInfo[] myPropertyInfo2 = type.GetProperties();
            string sqlparam = "";
            int paramCount = 0;
            foreach (PropertyInfo p in myPropertyInfo2)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo2[0], p))
                {
                    sqlparam += p.Name + "=@" + p.Name + " and ";
                    paramCount++;

                }
            }
            sqlparam = sqlparam.Substring(0, sqlparam.Length - 4);
            SqlParameter[] param = new SqlParameter[paramCount];
            int cNum = 0;
            foreach (PropertyInfo p in myPropertyInfo2)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo2[0], p))
                {
                    param[cNum] = DBHelper.GetSqlParameter(p);
                    param[cNum].Value = p.GetValue(obj, null);
                    cNum++;

                }
            }
            string sql = string.Format("select * from (select *,row_number() over (order by " + myPropertyInfo2[0].Name + " desc) as row from {0} where {1}) {2} where row between {3} and {4}", tableName, sqlparam, tableName, PageSize * (NowPage - 1) + 1, NowPage * PageSize);
            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql, connection, trans, param);
            while (reader.Read())
            {
                var t = Activator.CreateInstance(obj.GetType());
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    if (DBHelper.NeedToParse(p))
                    {
                        string columnName = p.Name;
                        string PType = p.PropertyType.ToString();
                        DBHelper.Operator(p, PType, reader[columnName], t);
                    }
                }
                tList.Add(t);
            }
            reader.Close();
            recordCount = DBHelper.GetTableCountByKeys(tableName, sqlparam, ADO.DbType.MSSql, myPropertyInfo2[0].Name, connection, trans, param);
            if (recordCount % PageSize == 0)
            {
                PageCount = recordCount / PageSize;
            }
            else
            {
                PageCount = (recordCount / PageSize) + 1;
            }
            return tList;
        }

        /// <summary>
        /// 查询按某条件查询的所有记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<object> GetAllByKey(string key, string value, dynamic obj, SqlConnection connection, SqlTransaction trans)
        {
            List<object> tList = new List<object>();
            Type type = obj.GetType();
            PropertyInfo[] propertie = type.GetProperties();
            string tableName = obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1);
            string sql = string.Format("select  * from {0} where {1}=@{1} order by " + propertie[0].Name + " desc", tableName, key);

            int pIndex = 0;
            foreach (PropertyInfo p in propertie)
            {
                if (p.Name.Equals(key))
                {
                    break;
                }
                pIndex++;
            }
            SqlParameter[] param = new SqlParameter[1];
            param[0] = DBHelper.GetSqlParameter(propertie[pIndex]);
            param[0].Value = propertie[pIndex].GetValue(obj, null);

            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql, connection, trans, param);
            while (reader.Read())
            {
                var t = Activator.CreateInstance(obj.GetType());
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    if (DBHelper.NeedToParse(p))
                    {
                        string columnName = p.Name;
                        string PType = p.PropertyType.ToString();
                        DBHelper.Operator(p, PType, reader[columnName], t);
                    }
                }
                tList.Add(t);
            }
            reader.Close();

            return tList;
        }

        /// <summary>
        /// 查询一条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public object GetModel(dynamic obj, SqlConnection connection, SqlTransaction trans)
        {
            var t = Activator.CreateInstance(obj.GetType());
            Type type = obj.GetType();
            PropertyInfo[] propertie = type.GetProperties();
            string fullname = obj.ToString();
            string tableName = fullname.Substring(fullname.LastIndexOf(".") + 1);
            string sql = string.Format("select * from {0} where " + propertie[0].Name + "=@{1}", tableName, propertie[0].Name);
            SqlParameter[] param = new SqlParameter[1];
            param[0] = DBHelper.GetSqlParameter(propertie[0]);
            param[0].Value = propertie[0].GetValue(obj, null);

            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql, connection, trans);
            while (reader.Read())
            {
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    if (DBHelper.NeedToParse(p))
                    {
                        string columnName = p.Name;
                        string PType = p.PropertyType.ToString();
                        DBHelper.Operator(p, PType, reader[columnName], t);
                    }
                }
            }
            reader.Close();

            return t;
        }

        /// <summary>
        /// 保存(sql)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public int Insert(dynamic obj, SqlConnection connection, SqlTransaction trans)
        {
            int id = -1;

            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("insert into {0} values(", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.PropertyInfoCouldInsert(p,myPropertyInfo[0]))
                {
                    sql += "@" + p.Name + ",";
                }
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += ");select @@identity";
            SqlParameter[] param = new SqlParameter[DBHelper.GetSystemNamespaceFieldCount(myPropertyInfo)];
            int it = 0;
            for (int i = 0; i < myPropertyInfo.Length; i++)
            {
                if (DBHelper.PropertyInfoCouldInsert(myPropertyInfo[i], myPropertyInfo[0]))
                {
                    param[it] = DBHelper.GetSqlParameter(myPropertyInfo[i]);
                    param[it].Value = myPropertyInfo[i].GetValue(obj, null);
                    it++;
                }
            }
            id = Convert.ToInt32(DbHelperSQL.GetSingle(connection, trans, sql, param));

            return id;
        }

        /// <summary>
        /// 查询按某些条件查询的头几条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<object> GetTopList(int count, dynamic obj, SqlConnection connection, SqlTransaction trans)
        {
            List<object> tList = new List<object>();
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("select  top {0} * from {1} where ", count, obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            int paramCount = 0;
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo[0], p))
                {
                    sql += p.Name + "=@" + p.Name + " and ";
                    paramCount++;
                }
            }
            sql = sql.Substring(0, sql.Length - 4);
            sql += " order by " + myPropertyInfo[0].Name + " desc";
            SqlParameter[] param = new SqlParameter[paramCount];
            int cNum = 0;
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo[0], p))
                {
                    param[cNum] = DBHelper.GetSqlParameter(p);
                    param[cNum].Value = p.GetValue(obj, null);
                    cNum++;

                }
            }
            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql, connection, trans, param);
            while (reader.Read())
            {
                var t = Activator.CreateInstance(obj.GetType());
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    if (DBHelper.NeedToParse(p))
                    {
                        string columnName = p.Name;
                        string PType = p.PropertyType.ToString();
                        DBHelper.Operator(p, PType, reader[columnName], t);
                    }
                }
                tList.Add(t);
            }
            reader.Close();
            return tList;
        }



        /// <summary>
        /// 执行SQL语句，返回影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connection"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public int ExecuteScalar(string sql, SqlConnection connection, SqlTransaction trans)
        {
            int count = DbHelperSQL.ExecuteSql(connection, trans, sql);
            return count;
        }

        /// <summary>
        /// 执行SQL语句，返回SqlDataReader，需要手动关闭
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connection"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public dynamic ExecuteReader(string sql, SqlConnection connection, SqlTransaction trans)
        {
            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql, connection, trans);
            return reader;
        }
        /// <summary>
        /// 执行SQL语句，返回查询字段
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="connection"></param>
        /// <param name="trans"></param>
        /// <returns></returns>
        public dynamic GetSingle(string sql, SqlConnection connection, SqlTransaction trans)
        {
            var query = DbHelperSQL.GetSingle(connection, trans, sql);
            return query;
        }

        /// <summary>
        /// 查询满足条件的记录是否唯一存在
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool IsExist(object obj, SqlConnection connection, SqlTransaction trans)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("select count(" + myPropertyInfo[0].Name + ") from {0} where ", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            int paramCount = 0;
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo[0], p))
                {
                    sql += p.Name + "=@" + p.Name + " and ";
                    paramCount++;

                }
            }
            sql = sql.Substring(0, sql.Length - 4);
            SqlParameter[] param = new SqlParameter[paramCount];
            int cNum = 0;
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo[0], p))
                {
                    param[cNum] = DBHelper.GetSqlParameter(p);
                    param[cNum].Value = p.GetValue(obj, null);
                    cNum++;

                }
            }
            int id = Convert.ToInt32(DbHelperSQL.GetSingle(connection, trans, sql, param));

            if (id == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 查询满足条件的记录是否存在
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool IsJustExist(dynamic obj, SqlConnection connection, SqlTransaction trans)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("select count(" + myPropertyInfo[0].Name + ") from {0} where ", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            int paramCount = 0;
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo[0], p))
                {
                    sql += p.Name + "=@" + p.Name + " and ";
                    paramCount++;

                }
            }
            sql = sql.Substring(0, sql.Length - 4);
            SqlParameter[] param = new SqlParameter[paramCount];
            int cNum = 0;
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo[0], p))
                {
                    param[cNum] = DBHelper.GetSqlParameter(p);
                    param[cNum].Value = p.GetValue(obj, null);
                    cNum++;

                }
            }
            int id = Convert.ToInt32(DbHelperSQL.GetSingle(connection, trans, sql, param));

            if (id > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 查询所有满足某些条件的数据
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public List<object> GetSomeObject(dynamic obj, SqlConnection connection, SqlTransaction trans)
        {
            List<object> tList = new List<object>();
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("select * from {0} where ", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            int paramCount = 0;
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo[0], p))
                {
                    sql += p.Name + "=@" + p.Name + " and ";
                    paramCount++;

                }
            }
            sql = sql.Substring(0, sql.Length - 4);
            sql += " order by " + myPropertyInfo[0].Name + " desc";
            SqlParameter[] param = new SqlParameter[paramCount];
            int cNum = 0;
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo[0], p))
                {
                    param[cNum] = DBHelper.GetSqlParameter(p);
                    param[cNum].Value = p.GetValue(obj, null);
                    cNum++;

                }
            }
            SqlDataReader reader = DbHelperSQL.ExecuteReader(sql, connection, trans, param);
            while (reader.Read())
            {
                var t = Activator.CreateInstance(obj.GetType());
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    if (DBHelper.NeedToParse(p))
                    {
                        string columnName = p.Name;
                        string PType = p.PropertyType.ToString();
                        DBHelper.Operator(p, PType, reader[columnName], t);
                    }
                }
                tList.Add(t);
            }
            reader.Close();
            return tList;
        }

        /// <summary>
        /// 编辑一个对象并保存
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="nameSpace"></param>
        public void Edit(dynamic obj, SqlConnection connection, SqlTransaction trans)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("update {0} set ", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            int ad = 0;
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.PropertyInfoCouldEdit(obj, myPropertyInfo[0], p))
                {
                    sql += p.Name + "=@" + p.Name + ",";
                    ad++;
                }
            }
            sql = sql.Substring(0, sql.Length - 1);
            sql += " where " + myPropertyInfo[0].Name + "=" + myPropertyInfo[0].GetValue(obj, null);
            SqlParameter[] param = new SqlParameter[ad];
            int it = 0;
            for (int i = 0; i < myPropertyInfo.Length; i++)
            {
                if (DBHelper.PropertyInfoCouldEdit(obj, myPropertyInfo[0], myPropertyInfo[i]))
                {
                    param[it] = DBHelper.GetSqlParameter(myPropertyInfo[i]);
                    param[it].Value = myPropertyInfo[i].GetValue(obj, null);
                    it++;
                }
            }
            DbHelperSQL.ExecuteSql(connection, trans, sql, param);
        }

        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="obj">对象</param>
        public void Delete(dynamic obj, SqlConnection connection, SqlTransaction trans)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("delete from {0} where " + myPropertyInfo[0].Name + "={1}", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1), myPropertyInfo[0].Name);

            SqlParameter[] param = new SqlParameter[1];
            param[0] = DBHelper.GetSqlParameter(myPropertyInfo[0]);
            param[0].Value = myPropertyInfo[0].GetValue(obj, null);

            DbHelperSQL.ExecuteSql(connection, trans, sql, param);
        }

        /// <summary>
        /// 删除表里的所有内容
        /// </summary>
        /// <param name="obj"></param>
        public void DeleteAll(dynamic obj, SqlConnection connection, SqlTransaction trans)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("delete from {0} ", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            DbHelperSQL.ExecuteSql(connection, trans, sql);
        }

        /// <summary>
        /// 按照数据的某些字段的值删除记录
        /// </summary>
        /// <param name="obj">对象</param>
        public void DeleteColumByKey_Value(dynamic obj, SqlConnection connection, SqlTransaction trans)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string cols = "";
            int pIndex = 0;
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (DBHelper.ParamEnabled(obj, myPropertyInfo[0], p))
                {
                    cols = p.Name;
                    break;
                }
                else
                {
                    pIndex++;
                }
            }
            string sql = string.Format("delete from {0} where {1}=@{2}", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1), cols, cols);
            SqlParameter[] param = new SqlParameter[1];
            param[0] = DBHelper.GetSqlParameter(myPropertyInfo[pIndex]);
            param[0].Value = myPropertyInfo[pIndex].GetValue(obj, null);

            DbHelperSQL.ExecuteSql(connection, trans, sql, param);
        }
    }
}
